from sql import sqlseverDB
from tool import *
from datetime import datetime
import openpyxl.workbook
import re,difflib,psutil
ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'sumaitongpol', SQL_USER, SQL_PWD)

def get_similarity(str1, str2):
   return difflib.SequenceMatcher(None, str1, str2).quick_ratio()


def buCongYNCD():

    rs= ms.ExecQuery('''select itemid,sku1,sku2,title from YNBigData
                        where id in (select min(id) from YNBigData 
                        where (code is null or code='') and adsid!='手动添加' group by itemID)
                     ''')
    
    print(f'共 {len(rs)} 个出单itemid,需找随机码')
    for cd_itemid,cd_sku1,cd_sku2,cd_title in rs:
        cd_sku2=cd_sku2 if cd_sku2 else None
        rs_ph=ms.ExecQuery('''select itemid,sku1,sku2,yntitle,yntitleRcode from proshopeeph where id in
                                (select min(id) from proshopeeph where yntitle=? group by itemid)
                           ''',(cd_title,))
        print(f'{cd_itemid} 从原始数据匹配到 {len(rs_ph)} 个数据')

        if len(rs_ph)==0:
            print(f'{cd_itemid} 标题匹配不到,先用sku缩减范围,取标题相似度最高作为数据链')
            print(cd_sku1,cd_sku2)
            if not cd_sku2:
                rs_phmgs=ms.ExecQuery("select itemid,yntitle,yntitleRcode from proshopeeph where sku1=? and (sku2='' or sku2 is null)",(cd_sku1,))

            else:
                rs_phmgs=ms.ExecQuery('select itemid,yntitle,yntitleRcode from proshopeeph where sku1=? and sku2=?',(cd_sku1,cd_sku2))
            

            ph_pros=[[ph_itemid,yntitle,yntitleRcode,get_similarity(yntitle,cd_title)] for ph_itemid,yntitle,yntitleRcode in rs_phmgs if yntitle and get_similarity(yntitle,cd_title)>0.5]
            print(f'共有{len(rs_phmgs)} 条数据待比对标题,标题相似度大于50%的有 {len(ph_pros)} 条')

            if len(ph_pros)==0:
                print(f'{cd_itemid},匹配失败')
            
            else:
                ph_pros.sort(key= lambda x :x[-1],reverse=True)

                print(f'匹配到最大相似度的标题数据位:{ph_pros[0][-1]}')
                ppph_itemid,ppyntitle,ppyntitleRcode,ppxsd=ph_pros[0]
                rcode= re.search(r'.*\|(.{8})\|$',ppyntitleRcode).group(1)
                aff=ms.ExecNoQuery('update YNBigData set code=?,title=?,PHItemid=? where itemid=?',(rcode,ppyntitleRcode,ppph_itemid,cd_itemid))
                print(f'cd_itemid:{cd_itemid} => ph_itemid:{ppph_itemid},成功匹配随机码:{rcode},更新行数:{aff}')


        elif len(rs_ph)==1:
            ph_itemid,sku1,sku2,yntitle,yntitleRcode=rs_ph[0]
            rcode= re.search(r'.*\|(.{8})\|$',yntitleRcode).group(1)
            aff=ms.ExecNoQuery('update YNBigData set code=?,title=?,PHItemid=? where itemid=?',(rcode,yntitleRcode,ph_itemid,cd_itemid))
            print(f'cd_itemid:{cd_itemid} => ph_itemid:{ph_itemid},成功匹配随机码:{rcode},更新行数:{aff}')
        else:
            print(f'{cd_itemid} 多行匹配,通过sku进一步匹配')
            
            for ph_itemid,ph_sku1,ph_sku2,yntitle,yntitleRcode in rs_ph:
                rs_skus=ms.ExecQuery('select sku1,sku2 from proshopeeph where itemid=?',(ph_itemid,))
                flag=False
                for sku1,sku2 in rs_skus:
                    sku2= sku2 if sku2 else None
                    rcode= re.search(r'.*\|(.{8})\|$',yntitleRcode).group(1)
                    if sku1==cd_sku1 and sku2==cd_sku2:
                        
                        aff=ms.ExecNoQuery('update YNBigData set code=?,title=?,PHItemid=? where itemid=?',(rcode,yntitleRcode,ph_itemid,cd_itemid))

                        print(f'cd_itemid:{cd_itemid} => ph_itemid:{ph_itemid},成功匹配随机码:{rcode},更新行数:{aff}')
                        flag=True
                        break
                if flag:
                    break

all_shopee_pros=ms.ExecQuery('select itemid,yntitle,yntitleRcode from proshopeeph where id in (select min(id) from proshopeeph where yntitle is not null group by itemid)')

def find_code_by_title(cd_title,cd_sku1,cd_sku2=None):

    cd_sku2=cd_sku2 if cd_sku2 else None
    rs_ph=tms.ExecQuery('''select itemid,sku1,sku2,yntitle,yntitleRcode from proshopeeph where id in
                            (select min(id) from proshopeeph where yntitle=? group by itemid)
                        ''',(cd_title,))
    print(f' 从原始数据匹配到 {len(rs_ph)} 个数据')
    
    if len(rs_ph)==0:

        if cd_sku1:
            print(f' 标题匹配不到,先用sku缩减范围,取标题相似度最高作为数据链')
            print(cd_sku1,cd_sku2)
            if not cd_sku2:
                print('111')
                rs_phmgs=tms.ExecQuery("select itemid,yntitle,yntitleRcode from proshopeeph where sku1=? and (sku2='' or sku2 is null)",(cd_sku1,))

            else:
                print('222')
                rs_phmgs=tms.ExecQuery('select itemid,yntitle,yntitleRcode from proshopeeph where sku1=? and sku2=?',(cd_sku1,cd_sku2))
            

            ph_pros=[[ph_itemid,yntitle,yntitleRcode,get_similarity(yntitle,cd_title)] for ph_itemid,yntitle,yntitleRcode in rs_phmgs if yntitle and get_similarity(yntitle,cd_title)>=0.80]
            print(f'共有{len(rs_phmgs)} 条数据待比对标题,标题相似度大于80%的有 {len(ph_pros)} 条')

            if len(ph_pros)==0:
                pass
            
            else:
                ph_pros.sort(key= lambda x :x[-1],reverse=True)
                print(f'匹配到最大相似度的标题数据位:{ph_pros[0][-1]}')
                ppph_itemid,ppyntitle,ppyntitleRcode,ppxsd=ph_pros[0]
                rcode= re.search(r'.*\|(.{8})\|$',ppyntitleRcode).group(1)
                return (rcode,ppph_itemid)
        
        print('SKU匹配失败,通过数据表全匹配...')

        xsd=0
        for ppph_itemid,ppyntitle,ppyntitleRcode in all_shopee_pros:
            new_xsd=get_similarity(cd_title,ppyntitle)
            rcode= re.search(r'.*\|(.{8})\|$',ppyntitleRcode).group(1)
            if new_xsd>=0.90:
                print(f'与{ppph_itemid}相似度超过90,提前退出')
                return (rcode,ppph_itemid)
            if new_xsd>xsd:
                max_xsd_pro=(rcode,ppph_itemid)
                xsd=new_xsd
        print(f'最终匹配相似度为:{xsd}')
        return max_xsd_pro

    elif len(rs_ph)==1:
        ph_itemid,sku1,sku2,yntitle,yntitleRcode=rs_ph[0]
        rcode= re.search(r'.*\|(.{8})\|$',yntitleRcode).group(1)
        return (rcode,ph_itemid)
    
    else:
        print(f' 多行匹配,通过sku进一步匹配')
        for ph_itemid,ph_sku1,ph_sku2,yntitle,yntitleRcode in rs_ph:
            rs_skus=tms.ExecQuery('select sku1,sku2 from proshopeeph where itemid=?',(ph_itemid,))

            for sku1,sku2 in rs_skus:
                sku2= sku2 if sku2 else None
                rcode= re.search(r'.*\|(.{8})\|$',yntitleRcode).group(1)
                if sku1==cd_sku1 and sku2==cd_sku2:
                    return (rcode,ph_itemid)
        
        ph_itemid,ph_sku1,ph_sku2,yntitle,yntitleRcode=rs_ph[0]
        rcode= re.search(r'.*\|(.{8})\|$',yntitleRcode).group(1)
        
        return (rcode,ph_itemid)


def add_parrent_sku_to_llbb():

    rs=ms.ExecQuery('select item_id,min(product_name) ,min(Variation_Name) from YN_LLBB where parent_sku is null group by item_id ')
    task_count=len(rs)
    print(f'流量报表共有:{task_count} 个数据待补充菲律宾item_id')
    j=0
    for ll_itemid,ll_title,llsku in rs:

        print(f'开始更新第{j+1}个itemid:{ll_itemid},剩余:{task_count} 个')
        mat= re.search(r'.*\|(.{8})\|$',ll_title)
        if mat:
            ll_code=mat.group(1)
            rs_rcode=ms.ExecQuery('select itemid,RandomCode from RandomCodes where RandomCode=?',(ll_code,))
            if rs_rcode:
                ph_itemid,rcode=rs_rcode[0]
            else:
                sku1=None;sku2=None
                if llsku:
                    sku_list=llsku.split(',')
                    sku1=sku_list[0]
                    if len(sku_list)>1:
                        sku2=sku_list[1]
                code,ph_itemid=find_code_by_title(ll_title.replace(f'|{ll_code}|','').strip(),sku1,sku2)

        else:
            sku1=None;sku2=None
            if llsku:
                sku_list=llsku.split(',')
                sku1=sku_list[0]
                if len(sku_list)>1:
                    sku2=sku_list[1]
            code,ph_itemid=find_code_by_title(ll_title,sku1,sku2)
        j+=1
        task_count-=1
        if ph_itemid:
            aff=ms.ExecNoQuery('update YN_LLBB set parent_sku=? where parent_sku is null and item_id=?',(ph_itemid,ll_itemid))
            print(f'第{j}个itemid:{ll_itemid},匹配到菲律宾itemid:{ph_itemid},更新行数:{aff},剩余:{task_count} 个')
        else:
            print(f'第{j}个itemid:{ll_itemid},未匹配到,剩余:{task_count} 个')


add_parrent_sku_to_llbb()